﻿Imports System.Data.SqlClient
Public Class ClienteDAL

#Region "Incluir/Alterar"
    Public Function InserirAlterar(codigo As Integer, nome As String, endereco As String, num As String, bairro As String, cidade As String, uf As String,
                                   complemento As String, cep As String, email As String, obs As String, dtnascimento As Date, status As Integer,
                                   tpe_codigo As Integer, cpf As String, rg As String, cnpj As String, inscricao As String,
                                   telres As String, telcom As String, telcel As String, telfax As String) As Integer
        Dim cmd = New SqlCommand
        Try
            cmd.CommandType = CommandType.StoredProcedure
            'cmd.CommandText = "execute SPCliente @codigo,@nome,@endereco,@bairro,@cidade,@numero,@telres ,@telcom ,@telcel ,@telfax ,@cep,@uf,@status,@obs,@codtp,@cpf,@rg,@cnpj,@ie,@complemento,@dtnascimento,@email  "
            cmd.CommandText = "SPCliente"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = nome
            cmd.Parameters.Add(New SqlParameter("@endereco", SqlDbType.VarChar)).Value = endereco
            cmd.Parameters.Add(New SqlParameter("@bairro", SqlDbType.VarChar)).Value = bairro
            cmd.Parameters.Add(New SqlParameter("@cidade", SqlDbType.VarChar)).Value = cidade
            cmd.Parameters.Add(New SqlParameter("@numero", SqlDbType.VarChar)).Value = num
            cmd.Parameters.Add(New SqlParameter("@telres", SqlDbType.VarChar)).Value = telres
            cmd.Parameters.Add(New SqlParameter("@telcom", SqlDbType.VarChar)).Value = telcom
            cmd.Parameters.Add(New SqlParameter("@telcel", SqlDbType.VarChar)).Value = telcel
            cmd.Parameters.Add(New SqlParameter("@telfax", SqlDbType.VarChar)).Value = telfax
            cmd.Parameters.Add(New SqlParameter("@cep", SqlDbType.VarChar)).Value = cep
            cmd.Parameters.Add(New SqlParameter("@uf", SqlDbType.VarChar)).Value = uf
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = status
            cmd.Parameters.Add(New SqlParameter("@obs", SqlDbType.VarChar)).Value = obs
            cmd.Parameters.Add(New SqlParameter("@codtp", SqlDbType.Int)).Value = tpe_codigo
            cmd.Parameters.Add(New SqlParameter("@cpf", SqlDbType.VarChar)).Value = cpf
            cmd.Parameters.Add(New SqlParameter("@rg", SqlDbType.VarChar)).Value = rg
            cmd.Parameters.Add(New SqlParameter("@cnpj", SqlDbType.VarChar)).Value = cnpj
            cmd.Parameters.Add(New SqlParameter("@ie", SqlDbType.VarChar)).Value = inscricao
            cmd.Parameters.Add(New SqlParameter("@complemento", SqlDbType.VarChar)).Value = complemento
            cmd.Parameters.Add(New SqlParameter("@dtnascimento", SqlDbType.DateTime)).Value = Format(dtnascimento, "yyyy/MM/dd")
            cmd.Parameters.Add(New SqlParameter("@email", SqlDbType.VarChar)).Value = email
            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region

#Region "Buscar"
    Public Function buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0
                    cmd.CommandText = "select * from vw_VenCliente where cli_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from vw_VenCliente where cli_nome COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 2
                    cmd.CommandText = "select * from vw_VenCliente where cli_cpf = @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.BigInt)).Value = Val(Descricao)
                Case 3
                    cmd.CommandText = "select * from vw_VenCliente where cli_cnpj= @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.BigInt)).Value = Val(Descricao)
                Case 4
                    cmd.CommandText = "select * from vw_VenCliente where sta_descricao = @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
                Case Else
                    cmd.CommandText = "select * from vw_VenCliente order by cli_nome"
            End Select

            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

#Region "Valida exclusão"
    Public Function ValidaExcluir(ByVal Codigo As Integer, Status As Integer) As Boolean
        Dim cmd = New SqlCommand
        Dim tab As DataTable
        Dim ret As Boolean = True
        Try
            If Status <> 1 Then
                cmd.CommandText = "select*from Vendas.Ofi_Placa p inner join Vendas.Ofi_Cliente c on p.cli_codigo=c.cli_codigo where p.sta_codigo <> 3 and c.cli_codigo=@codigo and c.sta_codigo <> 3 "
                cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
                tab = objconexao.executaConsulta(cmd)
                If (tab.Rows.Count > 0) Then
                    ret = False
                End If
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
#End Region
End Class
